﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace HTComics.Data.Tools
{
    public class DataInserter
    {
        private string _connString;

        public DataInserter(string connString)
        {
            _connString = connString;
        }

        public void InsertData(DataTable dt, out string qs)
        {
            SqlConnection conn = new SqlConnection(_connString);
            SqlTransaction transaction;
            SqlCommand cmd;
            int comicId=0;
            StringBuilder objSql = new StringBuilder();
            Dictionary<string, int> cTypes = GetConditionTypes();
            string comicTitle = "";
            qs = "";

            try
            {
                conn.Open();
                transaction = conn.BeginTransaction();                

                for(int i=0; i <= dt.Rows.Count-1; i++)
                {
                    try
                    {
                        //Insert Comic Title
                        if (comicTitle != dt.Rows[i]["ComicTitle"].ToString())
                        {
                            comicTitle = dt.Rows[i]["ComicTitle"].ToString();

                            objSql = new StringBuilder();
                            objSql.Append("SP_INSERT_COMIC ");
                            objSql.Append("@TITLE='" + comicTitle + "'");

                            cmd = new SqlCommand(objSql.ToString(), conn, transaction);                            
                            comicId = Convert.ToInt32(cmd.ExecuteScalar());

                            qs += objSql.ToString() + "\n\n";
                        }

                        string ctId = dt.Rows[i]["CONDITION"].ToString();

                        objSql = new StringBuilder();
                        objSql.Append("SP_INSERT_ISSUE ");
                        objSql.Append("@COMIC_ID=" + comicId);

                        string issueNum = dt.Rows[i]["IssueNumber"].ToString();
                        issueNum = issueNum.TrimStart().TrimEnd();
                        issueNum = issueNum.Replace("/", "-");
                        issueNum = issueNum.Replace(" ", "-");
                        issueNum = issueNum.Replace(":", "-");

                        objSql.Append(",@ISSUE_NUMBER='" + issueNum + "'");
                        objSql.Append(",@QUANTITY=" + dt.Rows[i]["Quantity"]);
                        objSql.Append(",@CONDITION_ID=" + ctId);
                        objSql.Append(",@COST=" + dt.Rows[i]["Cost"]);

                        if (dt.Rows[i]["CoverPrice"] != DBNull.Value && !string.IsNullOrEmpty(dt.Rows[i]["CoverPrice"].ToString()))
                            objSql.Append(",@COVER_PRICE=" + dt.Rows[i]["CoverPrice"]);
                        else
                            objSql.Append(",@COVER_PRICE=0");

                        objSql.Append(",@SELLING_PRICE=" + dt.Rows[i]["SellingPrice"].ToString().Replace(",","."));

                        if (dt.Rows[i]["Notes"] != DBNull.Value && !string.IsNullOrEmpty(dt.Rows[i]["Notes"].ToString()))
                            objSql.Append(",@NOTES='" + dt.Rows[i]["Notes"] + "'");

                        if (dt.Rows[i]["StoryLines"] != DBNull.Value && !string.IsNullOrEmpty(dt.Rows[i]["StoryLines"].ToString()))
                            objSql.Append(",@STORYLINES='" + dt.Rows[i]["StoryLines"] + "'");

                        objSql.Append(",@WRITER='" + dt.Rows[i]["Writer"] + "'");
                        objSql.Append(",@ARTIST='" + dt.Rows[i]["Artist"] + "'");


                        if (dt.Rows[i]["CoverDate"].ToString() == "29/02/73")
                            dt.Rows[i]["CoverDate"] = "01/03/73";

                        objSql.Append(",@COVER_DATE='" + Convert.ToDateTime(dt.Rows[i]["CoverDate"]).ToString("yyyy-MM-dd") + "'");

                        if (dt.Rows[i]["CustomField1"] != DBNull.Value && !string.IsNullOrEmpty(dt.Rows[i]["CustomField1"].ToString()))
                            objSql.Append(",@CUSTOM_FIELD_1='" + dt.Rows[i]["CustomField1"] + "'");

                        objSql.Append(",@APPEARANCES='" + dt.Rows[i]["Appearances"] + "'");

                        if (dt.Rows[i]["Colorists"] != DBNull.Value && !string.IsNullOrEmpty(dt.Rows[i]["Colorists"].ToString()))
                            objSql.Append(",@COLORISTS='" + dt.Rows[i]["Colorists"] + "'");

                        objSql.Append(",@COVER_ARTIST='" + dt.Rows[i]["CoverArtist"] + "'");

                        if (dt.Rows[i]["ComicInker"] != DBNull.Value && !string.IsNullOrEmpty(dt.Rows[i]["ComicInker"].ToString()))
                            objSql.Append(",@COMIC_INKER='" + dt.Rows[i]["ComicInker"] + "'");

                        objSql.Append(",@EDITOR='" + dt.Rows[i]["Editor"] + "'");
                        objSql.Append(",@INKER='" + dt.Rows[i]["Inker"] + "'");

                        if (dt.Rows[i]["Letterer"] != DBNull.Value && !string.IsNullOrEmpty(dt.Rows[i]["Letterer"].ToString()))
                            objSql.Append(",@LETTERER='" + dt.Rows[i]["Letterer"] + "'");


                        cmd = new SqlCommand(objSql.ToString(), conn, transaction);
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                    }

                    qs += objSql.ToString() + "\n\n";
                }

                transaction.Commit();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }

        }

        public void InsertRow(DataRow row, out string query)
        {
            SqlConnection conn = new SqlConnection(_connString);
            SqlCommand cmd;
            int comicId = 0;
            StringBuilder objSql = new StringBuilder();
            Dictionary<string, int> cTypes = GetConditionTypes();
            string comicTitle = "";
            query = "";

            conn.Open();

            //Get Comic Id
            comicTitle = row["ComicTitle"].ToString();

            objSql = new StringBuilder();
            objSql.Append("SP_INSERT_COMIC ");
            objSql.Append("@TITLE='" + comicTitle + "'");

            cmd = new SqlCommand(objSql.ToString(), conn);
            comicId = Convert.ToInt32(cmd.ExecuteScalar());


            string ctId = row["CONDITION"].ToString();

            objSql = new StringBuilder();
            objSql.Append("SP_INSERT_ISSUE ");
            objSql.Append("@COMIC_ID=" + comicId);

            string issueNum = row["IssueNumber"].ToString();
            issueNum = issueNum.TrimStart().TrimEnd();
            issueNum = issueNum.Replace("/", "-");
            issueNum = issueNum.Replace(" ", "-");
            issueNum = issueNum.Replace(":", "-");

            objSql.Append(",@ISSUE_NUMBER='" + issueNum + "'");
            objSql.Append(",@QUANTITY=" + row["Quantity"]);
            objSql.Append(",@CONDITION_ID=" + ctId);
            objSql.Append(",@COST=" + row["Cost"]);

            if (row["CoverPrice"] != DBNull.Value && !string.IsNullOrEmpty(row["CoverPrice"].ToString()))
                objSql.Append(",@COVER_PRICE=" + row["CoverPrice"]);
            else
                objSql.Append(",@COVER_PRICE=0");

            objSql.Append(",@SELLING_PRICE=" + row["SellingPrice"].ToString().Replace(",", "."));

            if (row["Notes"] != DBNull.Value && !string.IsNullOrEmpty(row["Notes"].ToString()))
                objSql.Append(",@NOTES='" + row["Notes"] + "'");

            if (row["StoryLines"] != DBNull.Value && !string.IsNullOrEmpty(row["StoryLines"].ToString()))
                objSql.Append(",@STORYLINES='" + row["StoryLines"] + "'");

            objSql.Append(",@WRITER='" + row["Writer"] + "'");
            objSql.Append(",@ARTIST='" + row["Artist"] + "'");


            if (row["CoverDate"].ToString() == "29/02/73")
                row["CoverDate"] = "01/03/73";

            objSql.Append(",@COVER_DATE='" + Convert.ToDateTime(row["CoverDate"]).ToString("yyyy-MM-dd") + "'");

            if (row["CustomField1"] != DBNull.Value && !string.IsNullOrEmpty(row["CustomField1"].ToString()))
                objSql.Append(",@CUSTOM_FIELD_1='" + row["CustomField1"] + "'");

            objSql.Append(",@APPEARANCES='" + row["Appearances"] + "'");

            if (row["Colorists"] != DBNull.Value && !string.IsNullOrEmpty(row["Colorists"].ToString()))
                objSql.Append(",@COLORISTS='" + row["Colorists"] + "'");

            objSql.Append(",@COVER_ARTIST='" + row["CoverArtist"] + "'");

            if (row["ComicInker"] != DBNull.Value && !string.IsNullOrEmpty(row["ComicInker"].ToString()))
                objSql.Append(",@COMIC_INKER='" + row["ComicInker"] + "'");

            objSql.Append(",@EDITOR='" + row["Editor"] + "'");
            objSql.Append(",@INKER='" + row["Inker"] + "'");

            if (row["Letterer"] != DBNull.Value && !string.IsNullOrEmpty(row["Letterer"].ToString()))
                objSql.Append(",@LETTERER='" + row["Letterer"] + "'");

            query = objSql.ToString();

            cmd = new SqlCommand(objSql.ToString(), conn);
            cmd.ExecuteNonQuery();

            conn.Close();
        }
        

        private Dictionary<string, int> GetConditionTypes()
        {
            SqlConnection conn = new SqlConnection(_connString);
            SqlCommand cmd;
            Dictionary<string, int> cTypes = new Dictionary<string, int>();
            SqlDataReader reader;

            try
            {
                conn.Open();

                cmd = conn.CreateCommand();
                cmd.CommandText = "SP_GET_CONDITION_TYPES";

                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    cTypes.Add(reader["Code"].ToString(), Convert.ToInt32(reader["ConditionId"]));
                }

            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }

            return cTypes;
        }

        //private static bool CheckkNull(
    }
}
